import re
import openpyxl
from openpyxl.styles import Font, Alignment


def extract_strings_from_file(file_path):
    """从文本文件中提取所有双引号内的中文字符串并去重"""
    with open(file_path, "r", encoding="utf-8") as file:
        content = file.read()

    # 使用正则表达式匹配所有双引号内的内容
    pattern = re.compile(r'"(.*?)"')
    matches = pattern.findall(content)

    # 过滤出中文字符串（包含中文或中文字符的）并去重
    chinese_strings = set()  # 使用集合自动去重
    for s in matches:
        if any("\u4e00" <= char <= "\u9fff" for char in s):
            chinese_strings.add(s.strip())  # 去除前后空格

    # 转换回列表并按原始顺序排序（如果需要保持顺序）
    # 如果不需要保持顺序，可以直接返回 list(chinese_strings)
    unique_strings = []
    seen = set()
    for s in matches:
        stripped = s.strip()
        if (
            any("\u4e00" <= char <= "\u9fff" for char in stripped)
            and stripped not in seen
        ):
            unique_strings.append(stripped)
            seen.add(stripped)

    return unique_strings  # 或者 return list(chinese_strings) 如果不需要保持顺序


def write_to_excel(strings, output_file):
    """将提取的字符串写入Excel表格"""
    # 创建工作簿和工作表
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "待翻译字符串"

    # 设置表头
    ws["A1"] = "ID"
    ws["B1"] = "原始字符串(中文)"
    ws["C1"] = "翻译后的字符串(英文)"
    ws["D1"] = "备注"

    # 设置表头格式
    for col in range(1, 5):
        ws.cell(row=1, column=col).font = Font(bold=True)
        ws.cell(row=1, column=col).alignment = Alignment(horizontal="center")

    # 写入数据
    for i, string in enumerate(strings, start=2):
        ws.cell(row=i, column=1, value=i - 1)  # ID
        ws.cell(row=i, column=2, value=string)  # 原始字符串

        # 设置单元格格式
        ws.cell(row=i, column=2).alignment = Alignment(wrap_text=True)

    # 自动调整列宽
    for column in ws.columns:
        max_length = 0
        column_letter = column[0].column_letter
        for cell in column:
            try:
                if len(str(cell.value)) > max_length:
                    max_length = len(str(cell.value))
            except:
                pass
        adjusted_width = (max_length + 2) * 1.2
        ws.column_dimensions[column_letter].width = adjusted_width

    # 保存Excel文件
    wb.save(output_file)
    print(f"成功将{len(strings)}个字符串写入到{output_file}")


if __name__ == "__main__":
    input_file = r"sourceData.txt"  # 替换为你的输入文件路径
    output_file = r"translation_strings.xlsx"  # 输出Excel文件路径

    # 提取字符串
    strings = extract_strings_from_file(input_file)

    # 写入Excel
    write_to_excel(strings, output_file)
